/*
SELECT * FROM fn_load_customer_recieve_notification_emails(170)
*/
CREATE function fn_load_customer_recieve_notification_emails ( @customer_id int )
RETURNS @fn_results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Source varchar(50) )

AS
BEGIN
	declare @customer_name varchar(65)
	declare @salesOrder_contact_person varchar(50)
	declare @email_count int
	declare @contact_person varchar(50)
	declare @email varchar(50)
	declare @sourcePerson varchar(50)
	--declare @salesOrder_Id int

	declare @emailCursor CURSOR

 	declare @results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Source varchar(50) )
 	
	SELECT @customer_name = C_Name FROM Customer WHERE Customer_ID = @customer_id

	--DECLARE @PRINT_MSG VARCHAR (200)
	--SET @PRINT_MSG = ' @customer_id = ' + CONVERT(VARCHAR,@customer_id)+ ' @customer_name = ' + CONVERT(VARCHAR,@customer_name) + ' @shipping_address_id = ' + CONVERT(VARCHAR,@shipping_address_id) + ' @billing_address_id = ' + CONVERT(VARCHAR,@billing_address_id)
		
	---LOAD CUSTOMER CONTACT PERSON EMAIL
	SET @email_count = 0
	SET @emailCursor = CURSOR FOR SELECT Contact_Person, Email FROM Customer_Contact_Persons_Table WHERE  ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( Sales_Ack_Mail = 0 ) AND ( Customer_Id = @customer_id )
	OPEN @emailCursor FETCH NEXT FROM @emailCursor INTO @contact_person, @email 
 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'CUSTOMER-CONTACT' )

		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null		
 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	/*IF( @email_count = 0 )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'CUSTOMER-CONTACT' )--here contact name = customer_name 
		END		
	END*/
	set @contact_person = null
	set @email = null		
 	
	---LOAD CUSTOMER EMAIL
	/*IF( @salesOrder_contact_person = @customer_name )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'CUSTOMER' )--here contact name = customer_name 
		END
	
		set @contact_person = null
		set @email = null		
	END*/

	---LOAD CUSTOMER BILLING CONTACT PERSON EMAIL
	SET @email_count = 0
	SET @emailCursor = CURSOR FOR SELECT ACPT.Contact_Person, ACPT.Email FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Billing_Address_ID = ACPT.Address_Id WHERE (Email IS NOT NULL) AND (Email <> '') AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( Sales_Ack_Mail = 0 ) AND (C.Customer_ID = @customer_id)

	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person,@email

 	WHILE @@fetch_status = 0
 	BEGIN
		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'BILLING-CONTACT' )

		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null		
 		FETCH NEXT FROM @emailCursor INTO @contact_person,@email
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	/*IF( @email_count = 0 )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'BILLING-CONTACT' )--here contact name = customer_name 
		END		
	END*/

	set @contact_person = null
	set @email			= null	

	---LOAD CUSTOMER SHIPPING CONTACT PERSON EMAIL
	SET @email_count = 0
	SET @emailCursor = CURSOR FOR 
	SELECT ACPT.Contact_Person, ACPT.Email FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Shipping_Address_ID = ACPT.Address_Id WHERE  (Email IS NOT NULL) AND (Email <> '') AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( Sales_Ack_Mail = 0 ) AND (C.Customer_ID = @customer_id) 
	--UNION SELECT ACPT.Contact_Person, ACPT.Email FROM Customer_Shipping_Address_Table CSAT INNER JOIN Address_Contact_Persons_Table ACPT ON CSAT.Address_ID = ACPT.Address_Id WHERE  (Email IS NOT NULL) AND (Email <> '') AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( Sales_Ack_Mail = 0 ) AND  (CSAT.Customer_ID = @customer_id)

	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person,@email

 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'SHIPPING-CONTACT' )
		
		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null		
 		FETCH NEXT FROM @emailCursor INTO @contact_person,@email
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	/*IF( @email_count = 0 )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'SHIPPING-CONTACT' )--here contact name = customer_name 
		END		
	END*/	
	set @contact_person = null
	set @email			= null		

	insert @fn_results select * from @results

 	return
end
 